var mysql=require("mysql");

//-------------注册管理部分（唐茂）--------------
//用户名是否重复判断
exports.db_registerByName=function(rgtByName,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="SELECT  *FROM userlgy WHERE user_name = ?;";
    MyselfSql.query(sql,[rgtByName],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
};
//注册用户信息
exports.db_registerYourNews=function(new1,new2,new3,new4,new5,new6,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="INSERT into userlgy(user_name,user_password,idCardNumber,phone,email,reallyName) VALUES (?,?,?,?,?,?);";
    MyselfSql.query(sql,[new1,new2,new4,new5,new6,new3],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
}
//-------------这是登录部分--------------

exports.login_db=function(name,password,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT *FROM userlgy where user_name=? and user_password=?;";
    Mainmysql.query(sql,[name,password],function(error,data){
        if(data.length==0||error){
            res.send("error");
        }else{
            res.send("ok");
        }
    });
    Mainmysql.end();
};
//登录显示（唐茂）
exports.db_LoginBN=function(byName,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="SELECT  *FROM userlgy WHERE user_name = ?;";
    MyselfSql.query(sql,[byName],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
}
//查询下单日期（唐茂）
exports.db_TodayInfo=function(byTime,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="SELECT  *FROM order_form WHERE by_time = ?;";
    MyselfSql.query(sql,[byTime],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
};
//退订（唐茂）
exports.db_TuiDin=function(Din,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="DELETE from order_form where id=?;";
    MyselfSql.query(sql,[Din],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
};
//按定票日期查询(唐茂)
exports.db_ByInfo1=function(DanNum,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="SELECT * FROM order_form WHERE id=?;";
    MyselfSql.query(sql,[DanNum],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
};
exports.db_ByInfo2=function(DanC,DanName,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="SELECT * FROM order_form WHERE head_word=? AND by_name=?;";
    MyselfSql.query(sql,[DanC,DanName],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
};
//按日期查询显示订单（唐茂）
exports.db_Show1=function(showD,res){
    //打开数据库
    var MyselfSql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    //使用数据库
    var sql="SELECT  *FROM order_form WHERE id = ?;";
    MyselfSql.query(sql,[showD],function(error,data){
        if(error){
            res.send("error")
        }else{
            res.send(data)
        }
    });
    //关闭数据库
    MyselfSql.end();
};
//----------廖广元----------
//最近常用
exports.recently_db=function(user,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="select* from recentlycitylgy where userName=? ORDER BY r_time DESC;";
    Mainmysql.query(sql,[user],function(error,data){
        if(data.length==0||error){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//热门车站
exports.hot_db=function(req,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from hotcitylgy;";
    Mainmysql.query(sql,[],function(error,data){
        if(data.length==0||error){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//车站列表
exports.all_db=function(req,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from citylgy;";
    Mainmysql.query(sql,[],function(error,data){
        if(data.length==0||error){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//锚点
exports.miaoDian_db=function(val,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from citylgy WHERE c_Initials=?;";
    Mainmysql.query(sql,[val],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//最近常用的搜索
exports.recentlySearch_db=function(val,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from recentlycitylgy WHERE r_name like ?";
    Mainmysql.query(sql,[val],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//火热站台搜索
exports.hotSearch_db=function(val,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from hotcitylgy WHERE h_name like ?;";
    Mainmysql.query(sql,[val],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//全部站台搜索
exports.allSearch_db=function(val,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from citylgy WHERE c_name like ?;";
    Mainmysql.query(sql,[val],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//最近常用路线
exports.recRouteShow_db=function(user,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="select*from recroutelgy WHERE userName=? ORDER BY recordTime DESC;";
    Mainmysql.query(sql,[user],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//添加最近常用路线
exports.addRecRoute_db=function(user,start,end,date,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="select*from recroutelgy WHERE userName=? and startName=? and endName=?;";
    Mainmysql.query(sql,[user,start,end],function(error,data){
        if(data.length>0){
            var Mainmysql=mysql.createConnection({
                host:"localhost",
                user:"root",
                password:"",
                database:"test"
            });
            var sql="UPDATE recroutelgy set recordTime=? where userName=? and startName=? and endName=?;";
            Mainmysql.query(sql,[date,user,start,end],function(error,data){
                if(error){
                    console.log("修改最近路线时间失败");
                }else{
                    console.log("修改最近路线时间成功");
                }
            });
            Mainmysql.end();
        }else if(data.length==0){
            var Mainmysql=mysql.createConnection({
                host:"localhost",
                user:"root",
                password:"",
                database:"test"
            });
            var sql="INSERT INTO recroutelgy (startName,endName,userName,recordTime)values(?,?,?,?);";
            Mainmysql.query(sql,[start,end,user,date],function(error,data){
                if(error){
                    console.log("添加最近路线失败");
                }else{
                    console.log("添加最近路线成功");
                }
            });
            Mainmysql.end();
        }else{
            console.log("后台有问题");
        }
    });
    Mainmysql.end();
};

//查询车票
exports.lookupTicket_db=function(start,end,godate,goTimeFirst,goTimeLast,carInitialsChoose,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    if(carInitialsChoose=="全部"){
        var sql="SELECT *FROM ticketinf WHERE startName LIKE ? and endName LIKE ? AND goDate=? AND goTime>? and goTime<?;";
        Mainmysql.query(sql,[start,end,godate,goTimeFirst,goTimeLast],function(error,data){
            if(error||data.length==0){
                res.send("error");
            }else{
                res.send(data);
            }
        });
    }else if(carInitialsChoose=="其他"){
        var sql="SELECT *FROM ticketinf WHERE startName LIKE ? and endName LIKE ? AND goDate=? AND goTime BETWEEN ? AND ? and Initials NOT IN ('K','T','G','S');";
        Mainmysql.query(sql,[start,end,godate,goTimeFirst,goTimeLast],function(error,data){
            if(error||data.length==0){
                res.send("error");
            }else{
                res.send(data);
            }
        });
    }else{
        var sql="SELECT *FROM ticketinf WHERE startName LIKE ? and endName LIKE ? AND goDate=? AND goTime BETWEEN ? AND ? and Initials=?;";
        Mainmysql.query(sql,[start,end,godate,goTimeFirst,goTimeLast,carInitialsChoose],function(error,data){
            if(error||data.length==0){
                res.send("error");
            }else{
                res.send(data);
            }
        });
    }
    Mainmysql.end();
};

//添加最近常用车站
exports.addMyRecntly_db=function(seat,date,user,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="select*from recentlycitylgy where r_name=? and userName=?;";
    Mainmysql.query(sql,[seat,user],function(error,data){
        if(data.length>0){
            var Mainmysql=mysql.createConnection({
                host:"localhost",
                user:"root",
                password:"",
                database:"test"
            });
            var sql="UPDATE recentlycitylgy set r_time=? where r_name=? and userName=?;";
            Mainmysql.query(sql,[date,seat,user],function(error,data){
                if(error){
                    console.log("修改最近常用站台时间失败");
                    console.log(error);
                }else{
                    console.log("修改最近常用站台时间成功");
                }
            });
            Mainmysql.end();
        }else{
            var Mainmysql=mysql.createConnection({
                host:"localhost",
                user:"root",
                password:"",
                database:"test"
            });
            var sql="INSERT INTO recentlycitylgy (r_name,r_time,userName) VALUES(?,?,?);";
            Mainmysql.query(sql,[seat,date,user],function(error,data){
                if(error){
                    console.log("添加最近常用站台失败");
                }else{
                    console.log("添加最近常用站台成功");
                }
            });
            Mainmysql.end();
        }
    });
    Mainmysql.end();
};

//刷新订单信息
exports.falshTicket_db=function(id,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*FROM ticketinf WHERE id=?;";
    Mainmysql.query(sql,[id],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//加载用名字和省份证号
exports.checkName_db=function(user,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="SELECT*from userlgy WHERE user_name=?;";
    Mainmysql.query(sql,[user],function(error,data){
        if(error||data.length==0){
            res.send("error");
        }else{
            res.send(data);
        }
    });
    Mainmysql.end();
};

//修改票数
exports.amendVotes_db=function(count,myId,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql="UPDATE ticketinf set conut=? WHERE id=?;";
    Mainmysql.query(sql,[count,myId],function(error,data){
        if(error){
            res.send("修改票数失败");
        }else{
            res.send("修改票数成功");
        }
    });
    Mainmysql.end();
};

//查看是否买过本次车票
exports.lookupIfBuySend_db=function(reallyname,carName,myDate,arriveTime,startStation,endStation,buyDate,user,res){
    var Mainmysql=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"test"
    });
    var sql='SELECT*FROM order_form where by_name=? and head_word=? AND go_off=?'+
        'AND arrival_time=? and go_station=? AND arrival_station=? and by_time=?'+
        'and userName=?;';
    Mainmysql.query(sql,[reallyname,carName,myDate,arriveTime,startStation,endStation,buyDate,user],function(error,data){
        if(data.length>0){
            res.send("买过");
        }else if(data.length==0){
            var Mainmysql=mysql.createConnection({
                host:"localhost",
                user:"root",
                password:"",
                database:"test"
            });
            var sql='INSERT INTO order_form (by_name,head_word,go_off,arrival_time,go_station,arrival_station,by_time,userName)'+
                'VALUES (?,?,?,?,?,?,?,?);';
            Mainmysql.query(sql,[reallyname,carName,myDate,arriveTime,startStation,endStation,buyDate,user],function(error,data){
                if(error){
                    res.send("添加订单失败");
                }else{
                    res.send("添加订单成功");
                }
            });
            Mainmysql.end();
        }else{
            res.send("后台有错误");
        }
    });
    Mainmysql.end();
};









